﻿
using System;
using System.Collections.Generic;
using Family.Core.Model;
using NHibernate;
using NHibernate.LambdaExtensions;
using NHibernate.SqlCommand;
using NHibernate.Transform;
using Order = Family.Core.Model.Order;

namespace Family.Data.DBRepositories
{
    public class OrderRepository : Repository<Order>
    {
        public OrderRepository(ISession session)
            : base(session)
        {
        }

        public virtual Order GetOrderByDate(DateTime date)
        {
            var orders = session.CreateCriteria<Order>()
                .Add<Order>(o => o.Date == date)
                .List<Order>();
            return orders.Count == 0 ? null : orders[0];
        }

        public virtual IList<Category> GetCategoriesByOrderId(Int32 orderId)
        {
            Order o = null;
            OrderItem oi = null;
            Product p = null;
            Category c = null;

            return session.CreateCriteria(typeof (Category), () => c)
                .CreateCriteria(() => c.Products, () => p, JoinType.InnerJoin)
                .CreateCriteria(() => p.OrderItems, () => oi, JoinType.InnerJoin)
                .CreateCriteria(() => oi.Order, () => o, JoinType.InnerJoin)
                .Add<Order>(x => x.Id == orderId)
                .SetResultTransformer(Transformers.DistinctRootEntity)
                .List<Category>();
        }

        public virtual IList<OrderItem> GetItemsForOrderByCategoryId(Int32 orderId, Int32 categoryId)
        {
            Order o = null;
            OrderItem oi = null;
            Product p = null;
            Category c = null;

            return session.CreateCriteria(typeof (OrderItem), () => oi)
                .CreateCriteria(() => oi.Order, () => o, JoinType.InnerJoin)
                .Add<Order>(x => x.Id == orderId)
                .CreateCriteria(() => oi.Product, () => p, JoinType.InnerJoin)
                .CreateCriteria(() => p.Category, () => c, JoinType.InnerJoin)
                .Add<Category>(x => x.Id == categoryId)
                .List<OrderItem>();
        }

        public virtual Decimal GetAmountByCategoryForOrder(Int32 orderId, Int32 categoryId)
        {
            Order o = null;
            OrderItem oi = null;
            Product p = null;
            Category c = null;

            var criteria = session.CreateCriteria(typeof (OrderItem), () => oi)
                .SetProjection(LambdaProjection.Sum<OrderItem>(x => x.Amount))
                .CreateCriteria(() => oi.Order, () => o, JoinType.InnerJoin)
                .Add<Order>(x => x.Id == orderId)
                .CreateCriteria(() => oi.Product, () => p, JoinType.InnerJoin)
                .CreateCriteria(() => p.Category, () => c, JoinType.InnerJoin)
                .Add<Category>(x => x.Id == categoryId);
            var result = criteria.UniqueResult();

            return result == null ? 0 : (Decimal)result;
        }

        public virtual Decimal GetAmountByDate(DateTime date)
        {
            Order o = null;
            OrderItem oi = null;
            Product p = null;
            Category c = null;

            var criteria = session.CreateCriteria(typeof (OrderItem), () => oi)
                .SetProjection(LambdaProjection.Sum<OrderItem>(x => x.Amount))
                .CreateCriteria(() => oi.Order, () => o, JoinType.InnerJoin)
                .Add<Order>(x => x.Date == date);
            var result = criteria.UniqueResult();

            return result == null ? 0 : (Decimal)result;
        }

        public virtual IList<Order> GetOrdersByRange(DateTime from, DateTime to)
        {
            return session.CreateCriteria<Order>()
                .Add(SqlExpression.Between<Order>(o => o.Date, from, to))
                .List<Order>();
        }

        public Decimal GetAmountByRange(Int32 categoryId, Int32 productId, DateTime from, DateTime to)
        {
            Order o = null;
            OrderItem oi = null;
            Product p = null;
            Category c = null;

            var criteria = session.CreateCriteria(typeof (OrderItem), () => oi)
                .SetProjection(LambdaProjection.Sum<OrderItem>(x => x.Amount))
                .CreateCriteria(() => oi.Product, () => p, JoinType.InnerJoin);

            if (productId != 0)
            {
                criteria = criteria.Add<Product>(x => x.Id == productId);
            }
            else if (categoryId != 0)
            {
                criteria = criteria.CreateCriteria(() => p.Category, () => c, JoinType.InnerJoin)
                    .Add<Category>(x => x.Id == categoryId);
            }

            criteria = criteria.CreateCriteria(() => oi.Order, () => o, JoinType.InnerJoin)
                .Add(SqlExpression.Between<Order>(x => x.Date, from, to));
            var result = criteria.UniqueResult();

            return result == null ? 0 : (Decimal) result;
        }
    }
}
